import {
  formMetaProps
} from 'element-plus'
import Excel from 'exceljs'

import FileSaver from 'file-saver'

export const exportExcel = async (name, luckysheet) => {
  // 获取 buffer
  let buffer = await getBuffer(luckysheet)
  download(name, buffer)
}



/**
 *  使用 fileSaver 进行文件保存操作
 * @param {Buffer} buffer
 */
function download(name, buffer) {
  try {
    const blob = new Blob([buffer], {
      type: 'application/vnd.ms-excel;charset=utf-8'
    })
    FileSaver.saveAs(blob, `${name}.xlsx`)
    ElMessage.success('文件导出成功')
  } catch (error) {
    ElMessage.error('文件导出失败')
  }
}

/**
 *
 * @param { Array as luckysheet.getluckysheetfile() } luckysheet
 * @returns
 */
async function getBuffer(luckysheet) {
  // 参数为luckysheet.getluckysheetfile()获取的对象
  // 1.创建工作簿，可以为工作簿添加属性
  const workbook = new Excel.Workbook()
  // 2.创建表格，第二个参数可以配置创建什么样的工作表
  luckysheet.every(function(table) {
    if (table.data.length === 0) return true
    const worksheet = workbook.addWorksheet(table.name)
    // 3.设置单元格合并,设置单元格边框,设置单元格样式,设置值
    setStyleAndValue(table.data, worksheet)
    // setMerge(table.config.merge, worksheet)
    setBorder(table.config.borderInfo, worksheet)
    return true
  })
  // 4.写入 buffer
  const buffer = await workbook.xlsx.writeBuffer()
  return buffer
}

var setMerge = function(luckyMerge = {}, worksheet) {
  const mergearr = Object.values(luckyMerge)
  mergearr.forEach(function(elem) {
    // elem格式：{r: 0, c: 0, rs: 1, cs: 2}
    // 按开始行，开始列，结束行，结束列合并（相当于 K10:M12）
    worksheet.mergeCells(elem.r + 1, elem.c + 1, elem.r + elem.rs, elem.c + elem.cs)
  })
}

var setBorder = function(luckyBorderInfo, worksheet) {
  if (!Array.isArray(luckyBorderInfo)) {
    return
  }
  // console.log('luckyBorderInfo', luckyBorderInfo)
  luckyBorderInfo.forEach(function(elem) {
    // 现在只兼容到borderType 为range的情况
    // console.log('ele', elem)
    if (elem.rangeType === 'range') {
      let border = borderConvert(elem.borderType, elem.style, elem.color)
      let rang = elem.range[0]
      // console.log('range', rang)
      let row = rang.row
      let column = rang.column
      for (let i = row[0] + 1; i < row[1] + 2; i++) {
        for (let y = column[0] + 1; y < column[1] + 2; y++) {
          worksheet.getCell(i, y).border = border
        }
      }
    }
    if (elem.rangeType === 'cell') {
      // col_index: 2
      // row_index: 1
      // b: {
      //   color: '#d0d4e3'
      //   style: 1
      // }
      const {
        col_index,
        row_index
      } = elem.value
      const borderData = Object.assign({}, elem.value)
      delete borderData.col_index
      delete borderData.row_index
      let border = addborderToCell(borderData, row_index, col_index)
      // console.log('bordre', border, borderData)
      worksheet.getCell(row_index + 1, col_index + 1).border = border
    }
    // console.log(rang.column_focus + 1, rang.row_focus + 1)
    // worksheet.getCell(rang.row_focus + 1, rang.column_focus + 1).border = border
  })
}
var setStyleAndValue = function(cellArr, worksheet) {
  if (!Array.isArray(cellArr)) {
    return
  }
  cellArr.forEach(function(row, rowid) {
    // const dbrow = worksheet.getRow(rowid+1);
    // //设置单元格行高,默认乘以1.2倍
    // dbrow.height=luckysheet.getRowHeight([rowid])[rowid]*1.2;
    row.every(function(cell, columnid) {
      if (rowid == 0) {
        const dobCol = worksheet.getColumn(columnid + 1)
        //设置单元格列宽除以8
        dobCol.width = luckysheet.getColumnWidth([columnid])[columnid] / 8
      }
      if (!cell) {
        return true
      }
      //设置背景色
      let bg = cell.bg || '#FFFFFF' //默认white
      bg = bg === 'yellow' ? 'FFFF00' : bg.replace('#', '')
      let fill = {
        type: 'pattern',
        pattern: 'solid',
        fgColor: {
          argb: bg
        }
      }
      let font = fontConvert(cell.ff, cell.fc, cell.bl, cell.it, cell.fs, cell.cl, cell.ul)
      let alignment = alignmentConvert(cell.vt, cell.ht, cell.tb, cell.tr)
      let value = ''

      if (cell.f) {
        value = {
          formula: cell.f,
          result: cell.v
        }
      } else if (!cell.v && cell.ct && cell.ct.s) {
        // xls转为xlsx之后，内部存在不同的格式，都会进到富文本里，即值不存在与cell.v，而是存在于cell.ct.s之后
        // value = cell.ct.s[0].v
        cell.ct.s.forEach((arr) => {
          value += arr.v
        })
      } else {
        value = cell.v
      }
      //  style 填入到_value中可以实现填充色
      let letter = createCellPos(columnid)
      let target = worksheet.getCell(letter + (rowid + 1))
      // console.log('1233', letter + (rowid + 1))
      for (const key in fill) {
        target.fill = fill
        break
      }
      target.font = font
      target.alignment = alignment
      target.value = value

      return true
    })
  })
}

var fontConvert = function(ff = 0, fc = '#000000', bl = 0, it = 0, fs = 10, cl = 0, ul = 0) {
  // luckysheet：ff(样式), fc(颜色), bl(粗体), it(斜体), fs(大小), cl(删除线), ul(下划线)
  const luckyToExcel = {
    0: '微软雅黑',
    1: '宋体（Song）',
    2: '黑体（ST Heiti）',
    3: '楷体（ST Kaiti）',
    4: '仿宋（ST FangSong）',
    5: '新宋体（ST Song）',
    6: '华文新魏',
    7: '华文行楷',
    8: '华文隶书',
    9: 'Arial',
    10: 'Times New Roman ',
    11: 'Tahoma ',
    12: 'Verdana',
    num2bl: function(num) {
      return num === 0 ? false : true
    }
  }
  // 出现Bug，导入的时候ff为luckyToExcel的val

  //设置字体颜色
  fc = fc === 'red' ? 'FFFF0000' : fc.replace('#', '')
  let font = {
    name: typeof ff === 'number' ? luckyToExcel[ff] : ff,
    family: 1,
    size: fs,
    color: {
      argb: fc
    },
    bold: luckyToExcel.num2bl(bl),
    italic: luckyToExcel.num2bl(it),
    underline: luckyToExcel.num2bl(ul),
    strike: luckyToExcel.num2bl(cl)
  }

  return font
}

var alignmentConvert = function(vt = 'default', ht = 'default', tb = 'default', tr = 'default') {
  // luckysheet:vt(垂直), ht(水平), tb(换行), tr(旋转)
  const luckyToExcel = {
    vertical: {
      0: 'middle',
      1: 'top',
      2: 'bottom',
      default: 'top'
    },
    horizontal: {
      0: 'center',
      1: 'left',
      2: 'right',
      default: 'left'
    },
    wrapText: {
      0: false,
      1: false,
      2: true,
      default: false
    },
    textRotation: {
      0: 0,
      1: 45,
      2: -45,
      3: 'vertical',
      4: 90,
      5: -90,
      default: 0
    }
  }

  let alignment = {
    vertical: luckyToExcel.vertical[vt],
    horizontal: luckyToExcel.horizontal[ht],
    wrapText: luckyToExcel.wrapText[tb],
    textRotation: luckyToExcel.textRotation[tr]
  }
  return alignment
}

var borderConvert = function(borderType, style = 1, color = '#000') {
  // 对应luckysheet的config中borderinfo的的参数
  if (!borderType) {
    return {}
  }
  const luckyToExcel = {
    type: {
      'border-all': 'all',
      'border-top': 'top',
      'border-right': 'right',
      'border-bottom': 'bottom',
      'border-left': 'left'
    },
    style: {
      0: 'none',
      1: 'thin',
      2: 'hair',
      3: 'dotted',
      4: 'dashDot', // 'Dashed',
      5: 'dashDot',
      6: 'dashDotDot',
      7: 'double',
      8: 'medium',
      9: 'mediumDashed',
      10: 'mediumDashDot',
      11: 'mediumDashDotDot',
      12: 'slantDashDot',
      13: 'thick'
    }
  }
  let template = {
    style: luckyToExcel.style[style],
    color: {
      argb: color.replace('#', '')
    }
  }
  let border = {}
  if (luckyToExcel.type[borderType] === 'all') {
    border['top'] = template
    border['right'] = template
    border['bottom'] = template
    border['left'] = template
  } else {
    border[luckyToExcel.type[borderType]] = template
  }
  // console.log('border', border)
  return border
}

function addborderToCell(borders, row_index, col_index) {
  let border = {}
  const luckyExcel = {
    type: {
      l: 'left',
      r: 'right',
      b: 'bottom',
      t: 'top'
    },
    style: {
      0: 'none',
      1: 'thin',
      2: 'hair',
      3: 'dotted',
      4: 'dashDot', // 'Dashed',
      5: 'dashDot',
      6: 'dashDotDot',
      7: 'double',
      8: 'medium',
      9: 'mediumDashed',
      10: 'mediumDashDot',
      11: 'mediumDashDotDot',
      12: 'slantDashDot',
      13: 'thick'
    }
  }

  // console.log('borders', borders)
  for (const bor in borders) {
    // console.log(bor)
    if (borders[bor].color.indexOf('rgb') === -1) {
      border[luckyExcel.type[bor]] = {
        style: luckyExcel.style[borders[bor].style],
        color: {
          argb: borders[bor].color.replace('#', '')
        }
      }
    } else {
      border[luckyExcel.type[bor]] = {
        style: luckyExcel.style[borders[bor].style],
        color: {
          argb: borders[bor].color
        }
      }
    }
  }

  return border
}

function createCellPos(n) {
  let ordA = 'A'.charCodeAt(0)

  let ordZ = 'Z'.charCodeAt(0)
  let len = ordZ - ordA + 1
  let s = ''
  while (n >= 0) {
    s = String.fromCharCode((n % len) + ordA) + s

    n = Math.floor(n / len) - 1
  }
  return s
}


export const handleData = async (fileList, mainFile) => {
  // console.log('fileList', fileList)

  let mainFiletransToData = luckysheet.transToData(mainFile.sheets[0].celldata);
  // let mainFiletransToData = mainFile.sheets[0].data;

  // console.log('123', mainFiletransToData)
  let err = [];
  let result = check(mainFiletransToData);
  if (result != 'report') {
    err.push(mainFile.sheets[0].name);
    return {
      'mainFile': '',
      'err': err
    };
  }
  let length = fileList.length;

  let type = [],
    fileData = [];
  if (length > 0) {

    // return length;
    // console.log(99999, mainFile.sheets[0]);
    for (let i = 0; i < length; i++) {
      let fileData_ = luckysheet.transToData(fileList[i].sheets[0].celldata);
      let type_ = check(fileData_);

      if (type_ == 0) {
        // console.log('type_ == 0',fileList[i].sheets[0])
        err.push(fileList[i].sheets[0].name);
      } else {
        type[i] = type_;
        fileData[i] = fileData_;
      }
    }
  }

  if (!fileData.length) {
    return {
      'mainFile': '',
      'err': err
    };
  }



  // console.log(123, mainFiletransToData);

  let left_early = new RegExp("早退");
  let be_late = new RegExp("迟到");
  let normal_t = new RegExp("正常");
  let rest_t = new RegExp("休息");
  let abstained = new RegExp("旷工");
  let absence = new RegExp("缺卡");
  let normal_rest = new RegExp("正常（休息）");
  let abnormal = new RegExp("设备异常");

  let year = new Date().getFullYear();
  let month_ = mainFiletransToData[1][0].m.slice(5, 7);

  // console.log('month', month_);
  // return;
  //away 出差
  let away_index = type.indexOf('away');
  let fileData_away = away_index != -1 ? fileData[away_index] : [];
  // console.log('fileData_away', fileData_away);
  //leave 请假
  let leave_index = type.indexOf('leave');
  let fileData_leave = leave_index != -1 ? fileData[leave_index] : [];
  // console.log('fileData_leave', fileData_leave);


  mainFiletransToData.forEach(function(value, index) {

     let hj = getDaysInMonth(year, month_);

    if (index > 3) {
      // console.log(73, value[73])


      //早退
      let tempFirst74 = $.extend(true, {}, value[73]);
      //迟到15分钟以内
      let tempFirst75 = $.extend(true, {}, value[73]);
      //迟到次数
      let tempFirst76 = $.extend(true, {}, value[73]);
      //旷工次数
      let tempFirst77 = $.extend(true, {}, value[73]);
      //缺卡次数
      let tempFirst78 = $.extend(true, {}, value[73]);
      //请假次数
      let tempFirst79 = $.extend(true, {}, value[73]);
      //出差次数
      let tempFirst80 = $.extend(true, {}, value[73]);
      //正常
      let tempFirst81 = $.extend(true, {}, value[73]);
      //正常（休息）
      let tempFirst82 = $.extend(true, {}, value[73]);
      //其他情况 -- 设备异常1次
      let tempFirst83 = $.extend(true, {}, value[73]);
      //合计
      let tempFirst84 = $.extend(true, {}, value[73]);



      let minu, zt = 0,
        cd_15 = 0,
        cd = 0,
        kg = 0,
        qk = 0,
        qj = 0,
        cc = 0,
        zc = 0,
        zcxx = 0,
        sbyc = 0;
      let name_key = value[0].m;
      let j = 43;
      let i = j + hj;
      // console.log('hj',hj);
      // console.log('i',i);
      // if (index == 143) {
      for (j; j < i; j++) {
        // if (j == 47) {
        //   console.log('m|' + index + "|" + j, value[j].m)
        //   console.log('v|' + index + "|" + j, value[j].v)
        // }
        let m = '';
        let m_t = 0;
        if (value[j].qp == 1) {
          m = value[j].m;
        } else {
          m = value[j].ct.s[0].v;
          m_t = 1;
        }


        // //早退
        // if (left_early.test(m)) {
        //   zt += 1;
        // }

        // //迟到
        // minu = 0;
        // if (be_late.test(m)) {
        //   minu = m.match(/\d+/g);
        //   minu = minu.length > 0 ? minu[0] : 0;
        //   // console.log('minu|' + index + "|" + j, minu)
        //   if (minu <= 15 && minu > 0) {
        //     // console.log('minu2|' + index + "|" + j, minu)
        //     cd_15 += 1;
        //   } else {
        //     cd += 1;
        //   }
        // }


        //旷工 or 缺卡 or 早退 or 迟到
        if (abstained.test(m) || absence.test(m) || left_early.test(m) || be_late.test(m)) {

          let day = (mainFiletransToData[3][j].ct.s[0].v).match(/\d+/g);
          let current_date = year + '-' + month_ + '-' + padDate(day);
          // console.log(j + '|day', day);
          // console.log(index + '|name_key', name_key);


          //出差
          let cc_ = 0;
          if (fileData_away.length) {
            // console.log('fileData_away begin');
            fileData_away.forEach(function(value_away, index_away) {


              if (index_away > 2 && value_away[1]) {
                // console.log('value_away[1].m', value_away[1].m);
                // console.log('name_key', name_key);
                if (value_away[1].m.trim() == name_key.trim() && value_away[20].m == "已结束") {
                  // console.log('current_date', current_date);

                  let begin_date_away = value_away[6].m;
                  let end_date_away = value_away[8].m;
                  // console.log('begin_date_away', begin_date_away);
                  // console.log('end_date_away', end_date_away);
                  // console.log('cc_result', isDateBetween(current_date, begin_date_away, end_date_away));
                  if (current_date && begin_date_away && end_date_away) {
                    if (isDateBetween(current_date, begin_date_away, end_date_away)) {
                      // if (value_away[1].m == "梁家达") {
                      //   console.log('value[j]',value[j])
                      // }
                      let begin_date_type_away = value_away[7].m;
                      let end_date_type_away = value_away[7].m;
                      // console.log('begin_date_type_away', begin_date_type_away);
                      // console.log('end_date_type_away', end_date_type_away);
                      cc_ = tally(current_date, begin_date_away, end_date_away, begin_date_type_away,
                        end_date_type_away);
                      // console.log('cc_', cc_);
                    }
                  }
                }
                // console.log('name_key_away', value_away[1].m);
              }
            })
          }


          //请假
          let qj_ = 0;
          if (cc_ == 0) {
            if (fileData_leave.length) {
              // console.log('fileData_leave begin');
              fileData_leave.forEach(function(value_leave, index_leave) {
                if (index_leave > 2 && value_leave[1]) {


                  if (value_leave[1].m.trim() == name_key.trim()) {

                    // let begin_date_leave = value_leave[7].m ? value_leave[7].m : value_leave[3].m;
                    // let end_date_leave = value_leave[9].m ? value_leave[9].m : value_leave[5].m;
                    let begin_date_leave = value_leave[3].m;
                    let end_date_leave = value_leave[5].m;

                    // if (value_leave[1].m == "宋飞") {
                    //   console.log('value_leave[1].m', value_leave[1].m);
                    //   console.log('name_key', name_key);
                    //   console.log('current_date', current_date);
                    //   console.log('begin_date_leave', begin_date_leave);
                    //   console.log('end_date_leave', end_date_leave);
                    //   console.log('qj_result', isDateBetween(current_date, begin_date_leave,end_date_leave));

                    // }



                    if (isDateBetween(current_date, begin_date_leave, end_date_leave)) {
                      // if (value_leave[1].m == "宋飞") {
                      // console.log('current_date', current_date);
                      // console.log('begin_date_leave', begin_date_leave);
                      // console.log('end_date_leave', end_date_leave);
                      // }
                      let begin_date_type_leave = value_leave[8].m ? value_leave[8].m : value_leave[4].m;
                      let end_date_type_leave = value_leave[10].m ? value_leave[10].m : value_leave[6].m;
                      // console.log('begin_date_type_leave', begin_date_type_leave);
                      // console.log('end_date_type_leave', end_date_type_leave);
                      qj_ = tally(current_date, begin_date_leave, end_date_leave, begin_date_type_leave,
                        end_date_type_leave);
                    }
                  }
                  // console.log('name_key_leave', value_leave[1].m);
                }
              })
            }
          }

          if (cc_ > 0) {
            cc += 1;
            // if(abstained.test(m)){
            if (m_t == 1) {
              value[j].ct.s[0].v = `${m} 出差`;
            } else {
              value[j].m = `${m} 出差`;
              value[j].v = `${m} 出差`;
            }
          } else if (qj_ > 0) {
            // console.log('qj_v',value[j].ct.s[0].v)
            qj += 1;
            // if(abstained.test(m)){
            // value[j].m = `${value[j].m} 请假`;
            // value[j].v = `${value[j].v} 请假`;
            if (m_t == 1) {
              value[j].ct.s[0].v = `${m} 请假`;
            } else {
              value[j].m = `${m} 请假`;
              value[j].v = `${m} 请假`;
            }
            // mainFiletransToData[index][j]={
            //   ...mainFiletransToData[index][j],
            //   m:`${value[j].m} 请假`,
            //   m:`${value[j].m} 请假`
            //   };
            // }

          } else {

            //早退
            if (left_early.test(m)) {
              zt += 1;
            }
            //迟到
            minu = 0;
            if (be_late.test(m)) {
              minu = m.match(/\d+/g);
              minu = minu.length > 0 ? minu[0] : 0;
              // console.log('minu|' + index + "|" + j, minu)
              if (minu <= 15 && minu > 0) {
                // console.log('minu2|' + index + "|" + j, minu)
                cd_15 += 1;
              } else {
                cd += 1;
              }
            }
            //旷工
            if (abstained.test(m)) {
              kg += 1;
            }
            //缺卡
            if (absence.test(m)) {
              qk += 1;
            }
          }

        }

        //正常
        if (normal_t.test(m) && !rest_t.test(m)) {
          zc += 1;
        }

        //正常（休息）
        if (normal_rest.test(m)) {
          zcxx += 1;
        }

        //其他情况 -- 设备异常1次
        if (abnormal.test(m)) {
          sbyc += 1;
          hj -= 1;
        }

      }
      // }

      //压入早退次数
      tempFirst74.m = zt;
      tempFirst74.v = zt;
      value.push(tempFirst74);
      //压入迟到15分钟以内次数
      tempFirst75.m = cd_15;
      tempFirst75.v = cd_15;
      value.push(tempFirst75);
      //压入迟到次数
      tempFirst76.m = cd; //显示值
      tempFirst76.v = cd; //原始值
      value.push(tempFirst76);
      //压入旷工次数
      tempFirst77.m = kg; //显示值
      tempFirst77.v = kg; //原始值
      value.push(tempFirst77);
      //压入缺卡次数
      tempFirst78.m = qk; //显示值
      tempFirst78.v = qk; //原始值
      value.push(tempFirst78);
      //压入请假次数
      tempFirst79.m = qj; //显示值
      tempFirst79.v = qj; //原始值
      value.push(tempFirst79);
      //压入出差次数
      tempFirst80.m = cc; //显示值
      tempFirst80.v = cc; //原始值
      value.push(tempFirst80);
      //压入正常次数
      tempFirst81.m = zc; //显示值
      tempFirst81.v = zc; //原始值
      value.push(tempFirst81);
      //压入正常（休息）
      tempFirst82.m = zcxx; //显示值
      tempFirst82.v = zcxx; //原始值
      value.push(tempFirst82);
      //其它情况 -- 设备异常1次
      tempFirst83.m = sbyc; //显示值
      tempFirst83.v = sbyc; //原始值
      value.push(tempFirst83);
      //压入合计
      tempFirst84.m = hj; //显示值
      tempFirst84.v = hj; //原始值
      value.push(tempFirst84);



      // tempDate.bg = "#F2F2F2";
      // let ct  = tempDate.ct;
      // ct.fa= "@";
      // ct.t= "s";
      // tempDate.ct = ct;//格式
      // tempDate.fc = "#7F7F7F";//字体颜色
      // tempDate.fs = 10;//字体大小
      // tempDate.ht = 1;//水平对齐 0 居中、1 左、2右
      // tempDate.vt = 10;//垂直对齐 0 中间、1 上、2下
      // tempDate.m = 10;//显示值
      // tempDate.v = 10;//原始值
      // tempDate.qp = 1;//
      // tempDate.tb = 1;//文本换行 0 截断、1溢出、2 自动换

      // value.push(tempDate);

      // console.log('index',index);
      // console.log('value',value[0].m);

    } else {
      if (index == 3) {
        let tempFirst74 = $.extend(true, {}, value[41]);
        tempFirst74.m = "早退";
        tempFirst74.v = "早退";
        value.push(tempFirst74);
        let tempFirst75 = $.extend(true, {}, value[41]);
        tempFirst75.m = "迟到15分钟内";
        tempFirst75.v = "迟到15分钟内";
        value.push(tempFirst75);
        let tempFirst76 = $.extend(true, {}, value[41]);
        tempFirst76.m = "迟到";
        tempFirst76.v = "迟到";
        value.push(tempFirst76);
        let tempFirst77 = $.extend(true, {}, value[41]);
        tempFirst77.m = "旷工";
        tempFirst77.v = "旷工";
        value.push(tempFirst77);
        let tempFirst78 = $.extend(true, {}, value[41]);
        tempFirst78.m = "缺卡";
        tempFirst78.v = "缺卡";
        value.push(tempFirst78);
        let tempFirst79 = $.extend(true, {}, value[41]);
        tempFirst79.m = "请假";
        tempFirst79.v = "请假";
        value.push(tempFirst79);
        let tempFirst80 = $.extend(true, {}, value[41]);
        tempFirst80.m = "出差";
        tempFirst80.v = "出差";
        value.push(tempFirst80);
        let tempFirst81 = $.extend(true, {}, value[41]);
        tempFirst81.m = "正常";
        tempFirst81.v = "正常";
        value.push(tempFirst81);
        let tempFirst82 = $.extend(true, {}, value[41]);
        tempFirst82.m = "正常（休息）";
        tempFirst82.v = "正常（休息）";
        value.push(tempFirst82);
        let tempFirst83 = $.extend(true, {}, value[41]);
        tempFirst83.m = "其它情况";
        tempFirst83.v = "其它情况";
        value.push(tempFirst83);
        let tempFirst84 = $.extend(true, {}, value[41]);
        tempFirst84.m = "合计";
        tempFirst84.v = "合计";
        value.push(tempFirst84);
        // console.log('value',value)
      } else {
        let tempHead;
        if (index < 2) {
          tempHead = {
            mc: {
              r: index,
              c: 0
            }
          }
        } else {
          tempHead = $.extend(true, {}, value[72]);
        }
        value.push(tempHead);
        value.push(tempHead);
        value.push(tempHead);
        value.push(tempHead);
        value.push(tempHead);
        value.push(tempHead);
        value.push(tempHead);
        value.push(tempHead);
        value.push(tempHead);
        value.push(tempHead);
        value.push(tempHead);
      }
    }
    // console.log(321, mainFiletransToData);
    // return;
  });

  mainFile.sheets[0].config.merge['0_0'] = {
    r: 0,
    c: 0,
    rs: 1,
    cs: 85
  };
  mainFile.sheets[0].config.merge['1_0'] = {
    r: 0,
    c: 0,
    rs: 1,
    cs: 85
  };
  mainFile.sheets[0].config.merge['2_74'] = {
    r: 2,
    c: 74,
    rs: 1,
    cs: 85
  };
  // mainFile.sheets[0].celldata = luckysheet.transToCellData(mainFiletransToData);
  mainFile.sheets[0].data = mainFiletransToData;
  // mainFile.sheets[0].data = mainFiletransToData;
  return {
    'mainFile': mainFile,
    'err': err
  };

}

export const handleData2 = async (mainFile) => {
  console.log('mainFile', mainFile)

  let mainFiletransToData2 = luckysheet.transToData(mainFile.sheets[0].celldata);
  // let mainFiletransToData = mainFile.sheets[0].data;
  // return;
  // console.log('123', mainFiletransToData)
  let err = [];
  let result = check(mainFiletransToData2);
  // conaole.log
  if (result != 'report2') {
    err.push(mainFile.sheets[0].name);
    return {
      'mainFile': '',
      'err': err
    };
  }
  // console.log('result',result);
  // return result;





  // console.log(123, mainFiletransToData);

  let left_early = new RegExp("早退");
  let be_late = new RegExp("迟到");
  let abstained = new RegExp("旷工");
  let absence = new RegExp("缺卡");
  let compassionate_leave = new RegExp("事假");
  let sick_leave = new RegExp("病假");
  let away = new RegExp("出差");
  let normal_t = new RegExp("正常");
  let normal_rest = new RegExp("正常（休息）");
  let field = new RegExp("外勤");
  let abnormal = new RegExp("设备异常");

  let year = mainFiletransToData2[0][0].m.slice(10, 14);
  let month_ = mainFiletransToData2[0][0].m.slice(16, 17);
  // console.log('year', year);
  // console.log('month_', month_);

  let hj_ = getDaysInMonth(year, month_);

  // console.log('hj', hj);


  mainFiletransToData2.forEach(function(value, index) {
    value = value.filter(item => item);
    if (index > 3) {
      //早退
      let tempFirst56 = $.extend(true, {}, value[55]);
      //迟到15分钟内
      let tempFirst57 = $.extend(true, {}, value[55]);
      //迟到
      let tempFirst58 = $.extend(true, {}, value[55]);
      //旷工
      let tempFirst59 = $.extend(true, {}, value[55]);
      //缺卡
      let tempFirst60 = $.extend(true, {}, value[55]);
      //事假
      let tempFirst61 = $.extend(true, {}, value[55]);
      //病假
      let tempFirst62 = $.extend(true, {}, value[55]);
      //出差
      let tempFirst63 = $.extend(true, {}, value[55]);
      //正常
      let tempFirst64 = $.extend(true, {}, value[55]);
      //外勤
      let tempFirst65 = $.extend(true, {}, value[55]);
      //其它情况 -设备异常
      let tempFirst66 = $.extend(true, {}, value[55]);
      //合计
      let tempFirst67 = $.extend(true, {}, value[55]);



      let minu, zt = 0,
        cd_15 = 0,
        cd = 0,
        kg = 0,
        qk = 0,
        sj = 0,
        bj = 0,
        cc = 0,
        zc = 0,
        wq = 0,
        sbyc = 0,
        hj = 0;
      let name_key = value[0].m;
      let j = 25;
      let i = j + hj_;
      console.log("i",j);
      console.log("hj_",hj_);
      for (j; j < i; j++) {
        // if (j == 47) {
        //   console.log('m|' + index + "|" + j, value[j].m)
        //   console.log('v|' + index + "|" + j, value[j].v)
        // }
        let m = '';
        let m_t = 0;
        let qt = 1;
        if (value[j].qp == 1) {
          m = value[j].m;
        } else {
          m = value[j].ct.s[0].v;
          m_t = 1;
        }


        //早退
        if (left_early.test(m)) {
          zt += 1;
          qt = 0;
          hj += 1;
        }

        //迟到
        minu = 0;
        if (be_late.test(m)) {
          minu = m.match(/\d+/g);
          minu = minu.length > 0 ? minu[0] : 0;
          // console.log('minu|' + index + "|" + j, minu)
          if (minu <= 15 && minu > 0) {
            // console.log('minu2|' + index + "|" + j, minu)
            cd_15 += 1;
            qt = 0;
            hj += 1;
          } else {
            cd += 1;
            qt = 0;
            hj += 1;
          }
        }

        let is_kg = 0;
        //旷工
        if (abstained.test(m)) {
          kg += 1;
          is_kg = 1;
          qt = 0;
          hj += 1;
        }
        //缺卡
        if (absence.test(m)) {
          qk += 1;
          qt = 0;
          hj += 1;
        }
        //事假
        if (compassionate_leave.test(m)) {
          sj += 1;
          qt = 0;
          hj += 1;
        }
        //病假
        if (sick_leave.test(m)) {
          bj += 1;
          qt = 0;
          hj += 1;
        }
        //出差
        if (away.test(m)) {
          if (is_kg) {
            kg -= 1;
            hj -= 1;
          }
          cc += 1;
          qt = 0;
          hj += 1;
        }

        //正常
        if (normal_t.test(m)) {
          zc += 1;
          qt = 0;
          hj += 1;
        }

        //外勤
        if (field.test(m)) {
          wq += 1;
          qt = 0;
          hj += 1;
        }

        //其他情况 -- 设备异常1次
        // if (abnormal.test(m)) {
        sbyc += qt;
        hj += qt;
        // }


      }


      //压入早退次数
      tempFirst56.m = zt;
      tempFirst56.v = zt;
      value.push(tempFirst56);
      //压入迟到15分钟以内次数
      tempFirst57.m = cd_15;
      tempFirst57.v = cd_15;
      value.push(tempFirst57);
      //压入迟到次数
      tempFirst58.m = cd; //显示值
      tempFirst58.v = cd; //原始值
      value.push(tempFirst58);
      //压入旷工次数
      tempFirst59.m = kg; //显示值
      tempFirst59.v = kg; //原始值
      value.push(tempFirst59);
      //压入缺卡
      tempFirst60.m = qk; //显示值
      tempFirst60.v = qk; //原始值
      value.push(tempFirst60);
      //压入事假次数
      tempFirst61.m = sj; //显示值
      tempFirst61.v = sj; //原始值
      value.push(tempFirst61);
      //压入病假次数
      tempFirst62.m = bj; //显示值
      tempFirst62.v = bj; //原始值
      value.push(tempFirst62);
      //压入出差次数
      tempFirst63.m = cc; //显示值
      tempFirst63.v = cc; //原始值
      value.push(tempFirst63);
      //压入正常次数
      tempFirst64.m = zc; //显示值
      tempFirst64.v = zc; //原始值
      value.push(tempFirst64);
      //压入外勤次数
      tempFirst65.m = wq; //显示值
      tempFirst65.v = wq; //原始值
      value.push(tempFirst65);
      //压入其它情况 -设备异常
      tempFirst66.m = sbyc; //显示值
      tempFirst66.v = sbyc; //原始值
      value.push(tempFirst66);
      //压入合计
      tempFirst67.m = hj; //显示值
      tempFirst67.v = hj; //原始值
      value.push(tempFirst67);


      // tempDate.bg = "#F2F2F2";
      // let ct  = tempDate.ct;
      // ct.fa= "@";
      // ct.t= "s";
      // tempDate.ct = ct;//格式
      // tempDate.fc = "#7F7F7F";//字体颜色
      // tempDate.fs = 10;//字体大小
      // tempDate.ht = 1;//水平对齐 0 居中、1 左、2右
      // tempDate.vt = 10;//垂直对齐 0 中间、1 上、2下
      // tempDate.m = 10;//显示值
      // tempDate.v = 10;//原始值
      // tempDate.qp = 1;//
      // tempDate.tb = 1;//文本换行 0 截断、1溢出、2 自动换

      // value.push(tempDate);

      // console.log('index',index);
      // console.log('value',value[0].m);

    } else {
      if (index == 3) {
        let tempFirst56 = $.extend(true, {}, value[24]);
        tempFirst56.m = "早退";
        tempFirst56.v = "早退";
        value.push(tempFirst56);
        let tempFirst57 = $.extend(true, {}, value[24]);
        tempFirst57.m = "迟到15分钟内";
        tempFirst57.v = "迟到15分钟内";
        value.push(tempFirst57);
        let tempFirst58 = $.extend(true, {}, value[24]);
        tempFirst58.m = "迟到";
        tempFirst58.v = "迟到";
        value.push(tempFirst58);
        let tempFirst59 = $.extend(true, {}, value[24]);
        tempFirst59.m = "旷工";
        tempFirst59.v = "旷工";
        value.push(tempFirst59);
        let tempFirst60 = $.extend(true, {}, value[24]);
        tempFirst60.m = "缺卡";
        tempFirst60.v = "缺卡";
        value.push(tempFirst60);
        let tempFirst61 = $.extend(true, {}, value[24]);
        tempFirst61.m = "事假";
        tempFirst61.v = "事假";
        value.push(tempFirst61);
        let tempFirst62 = $.extend(true, {}, value[24]);
        tempFirst62.m = "病假";
        tempFirst62.v = "病假";
        value.push(tempFirst62);
        let tempFirst63 = $.extend(true, {}, value[24]);
        tempFirst63.m = "出差";
        tempFirst63.v = "出差";
        value.push(tempFirst63);
        let tempFirst64 = $.extend(true, {}, value[24]);
        tempFirst64.m = "正常";
        tempFirst64.v = "正常";
        value.push(tempFirst64);
        let tempFirst65 = $.extend(true, {}, value[24]);
        tempFirst65.m = "外勤";
        tempFirst65.v = "外勤";
        value.push(tempFirst65);
        let tempFirst66 = $.extend(true, {}, value[24]);
        tempFirst66.m = "其它情况";
        tempFirst66.v = "其它情况";
        value.push(tempFirst66);
        let tempFirst67 = $.extend(true, {}, value[24]);
        tempFirst67.m = "合计";
        tempFirst67.v = "合计";
        value.push(tempFirst67);
        // console.log('value',value)
      } else {
        let tempHead;
        if (index < 2) {
          tempHead = {
            mc: {
              r: index,
              c: 0
            }
          }
        } else {
          tempHead = $.extend(true, {}, value[57]);
        }
        value.push(tempHead);
        value.push(tempHead);
        value.push(tempHead);
        value.push(tempHead);
        value.push(tempHead);
        value.push(tempHead);
        value.push(tempHead);
        value.push(tempHead);
        value.push(tempHead);
        value.push(tempHead);
        value.push(tempHead);
        value.push(tempHead);
      }
    }
    mainFiletransToData2[index] = value;
    // console.log(321, mainFiletransToData);
    // return;
  });

  mainFile.sheets[0].config.merge['0_0'] = {
    r: 0,
    c: 0,
    rs: 1,
    cs: 67
  };
  mainFile.sheets[0].config.merge['1_0'] = {
    r: 0,
    c: 0,
    rs: 1,
    cs: 67
  };
  mainFile.sheets[0].config.merge['2_56'] = {
    r: 2,
    c: 56,
    rs: 1,
    cs: 67
  };
  // mainFile.sheets[0].celldata = luckysheet.transToCellData(mainFiletransToData);
  mainFile.sheets[0].data = mainFiletransToData2;
  // mainFile.sheets[0].data = mainFiletransToData;
  return {
    'mainFile': mainFile,
    'err': err
  };

}

//
//

const exceltitle = [{ //away 出差
    'away': ['申请人', '出差人', '日期', '所在公司', '成本中心', '预定类型', '出发时间', '出发时间段', '返程时间', '返程时间段', '申请人预计出差天数', '预订方式', '飞机',
      '火车',
      '公司派车', '其他', '酒店', '出发城市-控制', '目的城市-控制', '申请类型', '流程状态_系统字段', '创建时间_系统字段'
    ]
  },
  { //leave 请假
    'leave': ['所在部门', '申请人', '请假类型', '开始日期', '开始时间段', '结束日期', '结束时间段', '实际开始日期', '实际开始时间段', '实际结束日期', '实际结束时间段',
      '请假天数', '实际请假天数', '明细表ID_系统字段'
    ]
  },
  // { //attendance 考勤
  //   'attendance': ['姓名', '考勤组', '部门', '工号', '职位', 'UserId', '出勤天数', '休息天数', '工作时长', '迟到次数', '迟到时长', '严重迟到次数',
  //     '严重迟到时长', '旷工迟到次数',
  //     '早退次数', '早退时长', '上班缺卡次数', '下班缺卡次数', '旷工天数', '出差时长', '外出时长', '加班-审批单统计', '加班时长-按加班规则计算', '考勤结果'
  //   ]
  // },
  {
    'report': ['姓名', '账号', '部门', '职务', '所属规则', '概况', '异常情况', '假勤情况', '加班情况', '打卡明细']
  },
  {
    'report2': ['姓名', '考勤组', '部门', '工号', '职位', 'UserId', '出勤天数', '休息天数', '工作时长', '迟到次数', '迟到时长', '严重迟到次数', '严重迟到时长',
      '旷工迟到次数', '早退次数', '早退时长', '上班缺卡次数', '下班缺卡次数', '旷工天数', '出差时长', '外出时长', '加班-审批单统计', '加班时长-按加班规则计算', '考勤结果'
    ]
  }
];



function check(arr) {
  // return 111;
  // console.log('arr[2]',arr[2]);
  let temp_exceltitle = arr[2].filter(item => item && item.v).map(i => i.v);

  // console.log('temp_exceltitle',temp_exceltitle);

  // return temp_exceltitle;
  let arr2 = exceltitle.find((v) => Object.values(v)[0].toString() === temp_exceltitle.toString());
  if (arr2) {
    // console.log(1, temp_exceltitle);
    return Object.keys(arr2)[0];
  } else {
    // console.log(0, temp_exceltitle);
    return 0;
  };

}

function isDateBetween(currentDate, startDate, endDate) {

  const current = stringToDate(currentDate);
  const start = stringToDate(startDate);
  const end = stringToDate(endDate);
  // console.log('current',current)
  // console.log('start',start)
  // console.log('end',end)
  return current >= start && current <= end;
}

function stringToDate(dateString) {
  // 匹配YYYY年MM月DD日的格式
  const regex = /^\d{4}年\d{1,2}月\d{1,2}日$/;
  if (!regex.test(dateString)) {
    let dt = new Date(dateString);
    dt.setHours(0, 0, 0, 0)
    return dt;
  }
  // 使用split方法按照年和月日的分隔符进行分割
  const yearPart = dateString.split('年')[0];
  const monthDayPart = dateString.split('年')[1].split('日')[0];

  // 再按照月和日的分隔符进行分割
  const monthPart = monthDayPart.split('月')[0];
  const dayPart = monthDayPart.split('月')[1];



  // 转换为数值
  const year = parseInt(yearPart, 10);
  const month = parseInt(monthPart, 10) - 1; // JavaScript中的月份是从0开始的
  const day = parseInt(dayPart, 10);

  // 创建Date对象
  const date = new Date(year, month, day);
  date.setHours(0, 0, 0, 0);
  // 检查日期是否有效
  if (isNaN(date.getTime())) {
    return 0;
  }

  return date;
}

function tally(currentDate, startDate, endDate, begin_date_type, end_date_type) {
  const current = new Date(currentDate);
  const start = new Date(startDate);
  const end = new Date(endDate);
  if (current == start && begin_date_type == "下午") {
    return 0.5;
  } else if (current == endDate && end_date_type == "上午") {
    return 0.5;
  } else {
    return 1;
  }
}


function padDate(date) {
  return date < 10 ? '0' + date : date.toString();
}

function getDaysInMonth(year, month) {
  var date = new Date();
  date.setFullYear(month);
  date.setMonth(month);
  date.setDate(0);
  return date.getDate();
}
